
/* ---------------------------------------------------------------------- */
/* INPUT_DAILY_FORECAST											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_DAILY_FORECAST')
BEGIN
	 DECLARE @reftable_1 nvarchar(60), @constraintname_1 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_DAILY_FORECAST'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_1, @constraintname_1
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_1+' drop constraint '+@constraintname_1)
	   FETCH NEXT from refcursor into @reftable_1, @constraintname_1
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_DAILY_FORECAST]
END


CREATE TABLE [INPUT_DAILY_FORECAST]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[IGEOGRAPHIC_REGION] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NULL,
	[SDESC_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SDESC_ENG] VARCHAR(MAX)(2147483646)  NULL,
	CONSTRAINT [INPUT_DAILY_FORECAST_PK] PRIMARY KEY ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION])
);

CREATE INDEX [PK_INPUT_DAILY_FORECAST] ON [INPUT_DAILY_FORECAST] ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION]);

/* ---------------------------------------------------------------------- */
/* INPUT_ELNINO_LANINA											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_ELNINO_LANINA')
BEGIN
	 DECLARE @reftable_2 nvarchar(60), @constraintname_2 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_ELNINO_LANINA'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_2, @constraintname_2
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_2+' drop constraint '+@constraintname_2)
	   FETCH NEXT from refcursor into @reftable_2, @constraintname_2
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_ELNINO_LANINA]
END


CREATE TABLE [INPUT_ELNINO_LANINA]
(
	[IELNINO_ID] INT  NOT NULL,
	[SISSUE_NO] CHAR(20)  NULL,
	[DTISSUE_TIME] DATETIME(16,3)  NULL,
	[DTSTARTTIME] DATETIME(16,3)  NULL,
	[DTENDTIME] DATETIME(16,3)  NULL,
	[SGENERAL_SITUATION] VARCHAR(MAX)(2147483646)  NULL,
	[SFORECAST] VARCHAR(MAX)(2147483646)  NULL,
	[SEFFECT] VARCHAR(MAX)(2147483646)  NULL,
	[SOBSERVATION] VARCHAR(MAX)(2147483646)  NULL,
	[SREMARK] VARCHAR(MAX)(2147483646)  NULL,
	[SIMAGE_FILE] VARCHAR(100)  NULL,
	[IUSER_ID] INT  NULL,
	CONSTRAINT [INPUT_ELNINO_LANINA_PK] PRIMARY KEY ([IELNINO_ID])
);

/* ---------------------------------------------------------------------- */
/* INPUT_FORTNIGHT_AGROMET											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_FORTNIGHT_AGROMET')
BEGIN
	 DECLARE @reftable_3 nvarchar(60), @constraintname_3 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_FORTNIGHT_AGROMET'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_3, @constraintname_3
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_3+' drop constraint '+@constraintname_3)
	   FETCH NEXT from refcursor into @reftable_3, @constraintname_3
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_FORTNIGHT_AGROMET]
END


CREATE TABLE [INPUT_FORTNIGHT_AGROMET]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NULL,
	[SWEATHER_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWEATHER_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SWEATHER_AGROMET_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWEATHER_AGROMET_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SREMIND_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SREMIND_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SDOC_LINK] VARCHAR(200)  NULL,
	CONSTRAINT [INPUT_FORTNIGHT_AGROMET_PK] PRIMARY KEY ([DTTIME_FORECAST])
);

/* ---------------------------------------------------------------------- */
/* INPUT_MONTHLY_FORECAST											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_MONTHLY_FORECAST')
BEGIN
	 DECLARE @reftable_4 nvarchar(60), @constraintname_4 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_MONTHLY_FORECAST'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_4, @constraintname_4
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_4+' drop constraint '+@constraintname_4)
	   FETCH NEXT from refcursor into @reftable_4, @constraintname_4
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_MONTHLY_FORECAST]
END


CREATE TABLE [INPUT_MONTHLY_FORECAST]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[IGEOGRAPHIC_REGION] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NOT NULL,
	[ITEMP_MAX_LOWBOUND] INT  NULL,
	[ITEMP_MAX_HIGHBOUND] INT  NULL,
	[ITEMP_MIN_LOWBOUND] INT  NULL,
	[ITEMP_MIN_HIGHBOUND] INT  NULL,
	[IWIND_DIRECTION] INT  NULL,
	[IWIND_SPEED_LOWBOUND] INT  NULL,
	[IWIND_SPEED_HIGHBOUND] INT  NULL,
	[IRAINFALL_LOWBOUND] INT  NULL,
	[IRAINFALL_HIGHBOUND] INT  NULL,
	[INUMDATE_RAINFALL_LOW] INT  NULL,
	[INUMDATE_RAINFALL_HIGH] INT  NULL,
	[IHUMIDITY_LOWBOUND] INT  NULL,
	[IHUMIDITY_HIGHBOUND] INT  NULL,
	[SDESC_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SDESC_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[IWEATHER_TYPE] INT  NULL,
	[IWAVE_TYPE] INT  NULL,
	[ITEMPERATURE_TYPE] INT  NULL,
	CONSTRAINT [INPUT_MONTHLY_FORECAST_PK] PRIMARY KEY ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION])
);

CREATE INDEX [PK_INPUT_MONTHLY_FORECAST] ON [INPUT_MONTHLY_FORECAST] ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION]);

/* ---------------------------------------------------------------------- */
/* INPUT_SEASONS_FORECAST											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_SEASONS_FORECAST')
BEGIN
	 DECLARE @reftable_5 nvarchar(60), @constraintname_5 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_SEASONS_FORECAST'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_5, @constraintname_5
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_5+' drop constraint '+@constraintname_5)
	   FETCH NEXT from refcursor into @reftable_5, @constraintname_5
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_SEASONS_FORECAST]
END


CREATE TABLE [INPUT_SEASONS_FORECAST]
(
	[ISEASON_ID] INT  NOT NULL,
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NOT NULL,
	[SDOC_LINK] VARCHAR(200)  NULL,
	[SDOC_LINK_ENG] VARCHAR(200)  NULL,
	[DTLAST_MODIFIED] DATETIME(16,3)  NULL,
	CONSTRAINT [INPUT_SEASONS_FORECAST_PK] PRIMARY KEY ([ISEASON_ID],[DTTIME_FORECAST])
);

CREATE INDEX [PK_INPUT_SEASONS_FORECAST] ON [INPUT_SEASONS_FORECAST] ([ISEASON_ID],[DTTIME_FORECAST]);

/* ---------------------------------------------------------------------- */
/* INPUT_SHIP_FORECAST											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_SHIP_FORECAST')
BEGIN
	 DECLARE @reftable_6 nvarchar(60), @constraintname_6 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_SHIP_FORECAST'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_6, @constraintname_6
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_6+' drop constraint '+@constraintname_6)
	   FETCH NEXT from refcursor into @reftable_6, @constraintname_6
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_SHIP_FORECAST]
END


CREATE TABLE [INPUT_SHIP_FORECAST]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[ISHIP_REGION] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NULL,
	[SDESC_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SDESC_ENG] VARCHAR(MAX)(2147483646)  NULL,
	CONSTRAINT [INPUT_SHIP_FORECAST_PK] PRIMARY KEY ([DTTIME_FORECAST],[ISHIP_REGION])
);

CREATE INDEX [PK_INPUT_SHIP_FORECAST] ON [INPUT_SHIP_FORECAST] ([DTTIME_FORECAST],[ISHIP_REGION]);

/* ---------------------------------------------------------------------- */
/* STORM_LEVELS											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'STORM_LEVELS')
BEGIN
	 DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'STORM_LEVELS'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_7, @constraintname_7
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_7+' drop constraint '+@constraintname_7)
	   FETCH NEXT from refcursor into @reftable_7, @constraintname_7
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [STORM_LEVELS]
END


CREATE TABLE [STORM_LEVELS]
(
	[ILEVEL] INT  NOT NULL,
	[SDESCRIPTION_ENG] VARCHAR(100)  NULL,
	[SDESCRIPTION_THAI] VARCHAR(100)  NULL,
	[ILOWER_BOUND_SPEED] INT  NULL,
	[IUPPER_BOUND_SPEED] INT  NULL,
	CONSTRAINT [STORM_LEVELS_PK] PRIMARY KEY ([ILEVEL])
);

/* ---------------------------------------------------------------------- */
/* STORM_TRACK_PREDICTION											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'STORM_TRACK_PREDICTION')
BEGIN
	 DECLARE @reftable_8 nvarchar(60), @constraintname_8 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'STORM_TRACK_PREDICTION'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_8, @constraintname_8
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_8+' drop constraint '+@constraintname_8)
	   FETCH NEXT from refcursor into @reftable_8, @constraintname_8
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [STORM_TRACK_PREDICTION]
END


CREATE TABLE [STORM_TRACK_PREDICTION]
(
	[ISTORM_ID] INT  NOT NULL,
	[DTTIME] DATETIME(16,3)  NOT NULL,
	[DTPREDICT_TIME] DATETIME(16,3)  NOT NULL,
	[ISPEED] INT  NULL,
	[ILEVEL] INT  NULL,
	[ILATITUDE_DEGREE] INT  NULL,
	[ILATITUDE_MINUTE] INT  NULL,
	[ILATITUDE_SECOND] INT  NULL,
	[ILONGITUDE_DEGREE] INT  NULL,
	[ILONGITUDE_MINUTE] INT  NULL,
	[ILONGITUDE_SECOND] INT  NULL,
	[IVARIANCE_UPWARD] INT  NULL,
	[IVARIANCE_DOWNWARD] INT  NULL,
	[IVARIANCE_FORWARD] INT  NULL,
	[SDOC_LINK] VARCHAR(200)  NULL,
	[IUSER_ID] INT  NULL,
	CONSTRAINT [STORM_TRACK_PREDICTION_PK] PRIMARY KEY ([ISTORM_ID],[DTTIME],[DTPREDICT_TIME])
);

CREATE INDEX [PK_STORM_TRACK_PREDICTION] ON [STORM_TRACK_PREDICTION] ([ISTORM_ID],[DTTIME],[DTPREDICT_TIME]);

/* ---------------------------------------------------------------------- */
/* STORM_TRACK											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'STORM_TRACK')
BEGIN
	 DECLARE @reftable_9 nvarchar(60), @constraintname_9 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'STORM_TRACK'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_9, @constraintname_9
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_9+' drop constraint '+@constraintname_9)
	   FETCH NEXT from refcursor into @reftable_9, @constraintname_9
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [STORM_TRACK]
END


CREATE TABLE [STORM_TRACK]
(
	[ISTORM_ID] INT  NOT NULL,
	[DTTIME] DATETIME(16,3)  NOT NULL,
	[ISPEED] INT  NULL,
	[ILEVEL] INT  NULL,
	[ILATITUDE_DEGREE] INT  NULL,
	[ILATITUDE_MINUTE] INT  NULL,
	[ILATITUDE_SECOND] INT  NULL,
	[ILONGITUDE_DEGREE] INT  NULL,
	[ILONGITUDE_MINUTE] INT  NULL,
	[ILONGITUDE_SECOND] INT  NULL,
	[SDOC_LINK] VARCHAR(200)  NULL,
	[IUSER_ID] INT  NULL,
	CONSTRAINT [STORM_TRACK_PK] PRIMARY KEY ([ISTORM_ID],[DTTIME])
);

CREATE INDEX [PK_STORM_TRACK] ON [STORM_TRACK] ([ISTORM_ID],[DTTIME]);

/* ---------------------------------------------------------------------- */
/* STORMS											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'STORMS')
BEGIN
	 DECLARE @reftable_10 nvarchar(60), @constraintname_10 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'STORMS'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_10, @constraintname_10
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_10+' drop constraint '+@constraintname_10)
	   FETCH NEXT from refcursor into @reftable_10, @constraintname_10
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [STORMS]
END


CREATE TABLE [STORMS]
(
	[ISTORM_ID] INT  NOT NULL,
	[SNAME_ENG] VARCHAR(100)  NULL,
	[SNAME_THAI] VARCHAR(100)  NULL,
	[SMEANING] VARCHAR(MAX)(2147483646)  NULL,
	[SORIGIN] VARCHAR(100)  NULL,
	CONSTRAINT [STORMS_PK] PRIMARY KEY ([ISTORM_ID])
);

/* ---------------------------------------------------------------------- */
/* TEMPERATURE_TYPE											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'TEMPERATURE_TYPE')
BEGIN
	 DECLARE @reftable_11 nvarchar(60), @constraintname_11 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'TEMPERATURE_TYPE'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_11, @constraintname_11
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_11+' drop constraint '+@constraintname_11)
	   FETCH NEXT from refcursor into @reftable_11, @constraintname_11
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [TEMPERATURE_TYPE]
END


CREATE TABLE [TEMPERATURE_TYPE]
(
	[ITEMPERATURE_TYPE] INT  NOT NULL,
	[SDESC_TH] VARCHAR(100)  NULL,
	[SDESC_ENG] VARCHAR(100)  NULL,
	CONSTRAINT [TEMPERATURE_TYPE_PK] PRIMARY KEY ([ITEMPERATURE_TYPE])
);

/* ---------------------------------------------------------------------- */
/* WAVE_TYPE											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'WAVE_TYPE')
BEGIN
	 DECLARE @reftable_12 nvarchar(60), @constraintname_12 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'WAVE_TYPE'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_12, @constraintname_12
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_12+' drop constraint '+@constraintname_12)
	   FETCH NEXT from refcursor into @reftable_12, @constraintname_12
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [WAVE_TYPE]
END


CREATE TABLE [WAVE_TYPE]
(
	[IWAVE_TYPE] INT  NOT NULL,
	[SDESC_TH] VARCHAR(100)  NULL,
	[SDESC_ENG] VARCHAR(100)  NULL,
	CONSTRAINT [WAVE_TYPE_PK] PRIMARY KEY ([IWAVE_TYPE])
);

/* ---------------------------------------------------------------------- */
/* WEATHER_TYPE											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'WEATHER_TYPE')
BEGIN
	 DECLARE @reftable_13 nvarchar(60), @constraintname_13 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'WEATHER_TYPE'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_13, @constraintname_13
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_13+' drop constraint '+@constraintname_13)
	   FETCH NEXT from refcursor into @reftable_13, @constraintname_13
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [WEATHER_TYPE]
END


CREATE TABLE [WEATHER_TYPE]
(
	[IWEATHER_TYPE] INT  NOT NULL,
	[SDESC_TH] VARCHAR(100)  NULL,
	[SDESC_ENG] VARCHAR(100)  NULL,
	CONSTRAINT [WEATHER_TYPE_PK] PRIMARY KEY ([IWEATHER_TYPE])
);

/* ---------------------------------------------------------------------- */
/* INPUT_3HOUR											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_3HOUR')
BEGIN
	 DECLARE @reftable_14 nvarchar(60), @constraintname_14 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_3HOUR'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_14, @constraintname_14
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_14+' drop constraint '+@constraintname_14)
	   FETCH NEXT from refcursor into @reftable_14, @constraintname_14
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_3HOUR]
END


CREATE TABLE [INPUT_3HOUR]
(
	[ISTATION_ID] INT  NOT NULL,
	[DTTIME] DATETIME(16,3)  NOT NULL,
	[X01] NUMERIC(11,1)  NULL,
	[X02] NUMERIC(11,2)  NULL,
	[E11] NUMERIC(11,2)  NULL,
	[E12] NUMERIC(11,2)  NULL,
	[E24] NUMERIC(11,1)  NULL,
	[E25] NUMERIC(11,1)  NULL,
	[E26] NUMERIC(11,1)  NULL,
	[E31] NUMERIC(11,0)  NULL,
	[E32] NUMERIC(11,2)  NULL,
	[E41] NUMERIC(11,2)  NULL,
	[E51] INT  NULL,
	[E61] CHAR(3)  NULL,
	[E62] INT  NULL,
	[E81] NUMERIC(11,2)  NULL,
	[E410] INT  NULL,
	[E501] INT  NULL,
	[SCURRENT_PHENOMENON] CHAR(2)  NULL,
	[SMSP_PHENOMENON] CHAR(1)  NULL,
	[SLSP_PHENOMENON] CHAR(1)  NULL,
	[BTSEND_NEWS] CHAR(1)  NULL,
	[SNEWS] VARCHAR(1000)  NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NULL,
	[SLOW_CLOUD_TYPE1] CHAR(1)  NULL,
	[NLOW_CLOUD_NUMBER1] INT  NULL,
	[NLOW_CLOUD_HEIGHT1] INT  NULL,
	[SLOW_CLOUD_TYPE2] CHAR(1)  NULL,
	[NLOW_CLOUD_NUMBER2] INT  NULL,
	[NLOW_CLOUD_HEIGHT2] INT  NULL,
	[SMIDDLE_CLOUD_TYPE] CHAR(1)  NULL,
	[NMIDDLE_CLOUD_NUMBER] INT  NULL,
	[SHIGH_CLOUD_TYPE] CHAR(1)  NULL,
	[NHIGH_CLOUD_NUMBER] INT  NULL,
	[NRAINFALL_24HOURS] NUMERIC(11,2)  NULL,
	[E21] NUMERIC(11,1)  NULL,
	[E22] NUMERIC(11,1)  NULL,
	[E301] NUMERIC(11,2)  NULL,
	[NBARO_PRESSURE] NUMERIC(11,2)  NULL,
	[NRAINFALL_6HOURS] NUMERIC(11,2)  NULL,
	[SNH] CHAR(1)  NULL,
	[SCL] CHAR(1)  NULL,
	[STR] CHAR(1)  NULL,
	[SNEWS_NOTE] VARCHAR(100)  NULL,
	[NRAINFALL_MIDNIGHT] NUMERIC(11,2)  NULL,
	[E401] NUMERIC(11,1)  NULL,
	[E402] NUMERIC(11,1)  NULL,
	[E403] NUMERIC(11,1)  NULL,
	[E404] NUMERIC(11,1)  NULL,
	[E405] NUMERIC(11,1)  NULL,
	[E406] NUMERIC(11,1)  NULL,
	[NNH] INT  NULL,
	[E42] NUMERIC(11,2)  NULL,
	[DTCREATE_TIME] DATETIME(16,3)  NULL,
	[IUSER_ID] INT  NULL,
	[SIPADDRESS] CHAR(15)  NULL,
	CONSTRAINT [INPUT_3HOUR_PK] PRIMARY KEY ([ISTATION_ID],[DTTIME])
);

CREATE INDEX [PK_INPUT_3HOUR] ON [INPUT_3HOUR] ([ISTATION_ID],[DTTIME]);

/* ---------------------------------------------------------------------- */
/* STATIONS											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'STATIONS')
BEGIN
	 DECLARE @reftable_15 nvarchar(60), @constraintname_15 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'STATIONS'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_15, @constraintname_15
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_15+' drop constraint '+@constraintname_15)
	   FETCH NEXT from refcursor into @reftable_15, @constraintname_15
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [STATIONS]
END


CREATE TABLE [STATIONS]
(
	[ISTATION_ID] INT  NOT NULL,
	[IWMO_STATION_NUMBER] INT  NULL,
	[SSTATION_NAME_ENG] VARCHAR(200)  NULL,
	[SSTATION_NAME_THAI] VARCHAR(200)  NULL,
	[SCHEIF_USER_NAME] VARCHAR(100)  NULL,
	[SADDRESS_NUMBER] VARCHAR(100)  NULL,
	[SVILLAGE] VARCHAR(100)  NULL,
	[SDISTRICT] VARCHAR(100)  NULL,
	[SREGION] VARCHAR(100)  NULL,
	[SPROVINCE] VARCHAR(100)  NULL,
	[SPOSTAL_CODE] VARCHAR(10)  NULL,
	[ILATITUDE_DEGREE] INT  NULL,
	[ILATITUDE_MINUTE] INT  NULL,
	[ILATITUDE_SECOND] DECIMAL(5,1)  NULL,
	[ILONGITUDE_DEGREE] INT  NULL,
	[ILONGITUDE_MINUTE] INT  NULL,
	[ILONGITUDE_SECOND] DECIMAL(5,1)  NULL,
	[DSTATION_HEIGHT] DECIMAL(11,3)  NULL,
	[DBAROMETER_HEIGHT] DECIMAL(11,3)  NULL,
	[DTHERMOMETER_HEIGHT] DECIMAL(11,2)  NULL,
	[DWINDARROW_HEIGHT] DECIMAL(11,2)  NULL,
	[DWINDMETER_HEIGHT] DECIMAL(11,2)  NULL,
	[DRAINGAUGE_HEIGHT] DECIMAL(11,2)  NULL,
	[BTHOURLY_SYNOP] INT  NULL,
	[BT3HOUR_SYNOP] INT  NULL,
	[BTDAILY_SYNOP] INT  NULL,
	[IGEOGRAPHIC_REGION] INT  NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NULL,
	[BTSUB_STATION_RAIN] INT  NULL,
	[BTHYDRO] INT  NULL,
	[BTAGRO] INT  NULL,
	[BTACTIVE] INT  NULL,
	[SPHONE_NUMBER] CHAR(9)  NULL,
	[ITIME_REPORT_PRESSURE] INT  NULL,
	[BTREPORT_SEA_STATE] INT  NULL,
	[BTREPORT_HOURLY] INT  NULL,
	[BTREPORT_3HOUR] INT  NULL,
	[BTREPORT_DAILY] INT  NULL,
	[BTREPORT_WATER_LEVEL] INT  NULL,
	[BTREPORT_MICRO_CLIMATE] INT  NULL,
	[BTREPORT_SUBSTATION_RAIN] INT  NULL,
	[ISTATION_TYPE] INT  NULL,
	[BTREPORT_SEA_VISIBILITY] INT  NULL,
	[DTCREATE_TIME] DATETIME(16,3)  NULL,
	[SCONTACT_NAME] VARCHAR(200)  NULL,
	[ITIME_REPORT_3HOUR] INT  NULL,
	[IUSER_ID] INT  NULL,
	[BTREPORT_CLIMAT] INT  NULL,
	[BTREPORT_UPPERAIR] INT  NULL,
	CONSTRAINT [STATIONS_PK] PRIMARY KEY ([ISTATION_ID])
);

/* ---------------------------------------------------------------------- */
/* EARTHQUAKE											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'EARTHQUAKE')
BEGIN
	 DECLARE @reftable_16 nvarchar(60), @constraintname_16 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'EARTHQUAKE'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_16, @constraintname_16
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_16+' drop constraint '+@constraintname_16)
	   FETCH NEXT from refcursor into @reftable_16, @constraintname_16
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [EARTHQUAKE]
END


CREATE TABLE [EARTHQUAKE]
(
	[IEARTHQUAKE_ID] INT  NOT NULL,
	[SORIGIN] VARCHAR(100)  NULL,
	[SORIGIN_ENG] VARCHAR(100)  NULL,
	[DTTIME] DATETIME(16,3)  NULL,
	[SPROVINCE_REF] VARCHAR(100)  NULL,
	[IDIRECTION_REF] INT  NULL,
	[IDISTANCE_REF] INT  NULL,
	[IDEEP_UNDERGROUND] INT  NULL,
	[NMAGNITUDE] DECIMAL(11,1)  NULL,
	[ILATITUDE_DEGREE] INT  NULL,
	[ILATITUDE_MINUTE] INT  NULL,
	[ILATITUDE_SECOND] INT  NULL,
	[ILONGITUDE_DEGREE] INT  NULL,
	[ILONGITUDE_MINUTE] INT  NULL,
	[ILONGITUDE_SECOND] INT  NULL,
	[SNOTE] VARCHAR(MAX)(2147483646)  NULL,
	[SNOTE_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[STITLE] VARCHAR(200)  NULL,
	[STITLE_ENG] VARCHAR(200)  NULL,
	[SIMAGE_FILE] VARCHAR(200)  NULL,
	[BTMANUAL] INT  NULL,
	CONSTRAINT [EARTHQUAKE_PK] PRIMARY KEY ([IEARTHQUAKE_ID])
);

/* ---------------------------------------------------------------------- */
/* GEOGRAPHIC_REGIONS											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'GEOGRAPHIC_REGIONS')
BEGIN
	 DECLARE @reftable_17 nvarchar(60), @constraintname_17 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'GEOGRAPHIC_REGIONS'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_17, @constraintname_17
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_17+' drop constraint '+@constraintname_17)
	   FETCH NEXT from refcursor into @reftable_17, @constraintname_17
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [GEOGRAPHIC_REGIONS]
END


CREATE TABLE [GEOGRAPHIC_REGIONS]
(
	[IGEOGRAPHIC_REGION] INT  NOT NULL,
	[SREGION_NAME_ENG] VARCHAR(50)  NULL,
	[SREGION_NAME_THAI] VARCHAR(50)  NULL,
	CONSTRAINT [GEOGRAPHIC_REGIONS_PK] PRIMARY KEY ([IGEOGRAPHIC_REGION])
);

/* ---------------------------------------------------------------------- */
/* PROVINCES											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND name='PROVINCES_FK_1')
	ALTER TABLE [PROVINCES] DROP CONSTRAINT [PROVINCES_FK_1];

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'PROVINCES')
BEGIN
	 DECLARE @reftable_18 nvarchar(60), @constraintname_18 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'PROVINCES'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_18, @constraintname_18
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_18+' drop constraint '+@constraintname_18)
	   FETCH NEXT from refcursor into @reftable_18, @constraintname_18
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [PROVINCES]
END


CREATE TABLE [PROVINCES]
(
	[IPROVINCE_ID] INT  NOT NULL,
	[SNAME_THAI] VARCHAR(100)  NOT NULL,
	[SNAME_ENG] VARCHAR(100)  NULL,
	[IGEOGRAPHIC_REGION] INT  NULL,
	[ISTATION_ID] INT  NULL,
	CONSTRAINT [PROVINCES_PK] PRIMARY KEY ([IPROVINCE_ID])
);

BEGIN
ALTER TABLE [PROVINCES] ADD CONSTRAINT [PROVINCES_FK_1] FOREIGN KEY ([IGEOGRAPHIC_REGION]) REFERENCES [GEOGRAPHIC_REGIONS] ([IGEOGRAPHIC_REGION])
END
;

/* ---------------------------------------------------------------------- */
/* INPUT_3MONTHS_FORECAST											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_3MONTHS_FORECAST')
BEGIN
	 DECLARE @reftable_19 nvarchar(60), @constraintname_19 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_3MONTHS_FORECAST'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_19, @constraintname_19
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_19+' drop constraint '+@constraintname_19)
	   FETCH NEXT from refcursor into @reftable_19, @constraintname_19
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_3MONTHS_FORECAST]
END


CREATE TABLE [INPUT_3MONTHS_FORECAST]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NOT NULL,
	[SDOC_LINK] VARCHAR(200)  NULL,
	[SDOC_LINK_ENG] VARCHAR(200)  NULL,
	[DTLAST_MODIFIED] DATETIME(16,3)  NULL,
	CONSTRAINT [INPUT_3MONTHS_FORECAST_PK] PRIMARY KEY ([DTTIME_FORECAST])
);

/* ---------------------------------------------------------------------- */
/* INPUT_7D_AGROMET											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_7D_AGROMET')
BEGIN
	 DECLARE @reftable_20 nvarchar(60), @constraintname_20 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_7D_AGROMET'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_20, @constraintname_20
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_20+' drop constraint '+@constraintname_20)
	   FETCH NEXT from refcursor into @reftable_20, @constraintname_20
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_7D_AGROMET]
END


CREATE TABLE [INPUT_7D_AGROMET]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[IGEOGRAPHIC_REGION] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NULL,
	[SEFFECT_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SEFFECT_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SWEATHER_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWEATHER_ENG] VARCHAR(MAX)(2147483646)  NULL,
	CONSTRAINT [INPUT_7D_AGROMET_PK] PRIMARY KEY ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION])
);

CREATE INDEX [PK_INPUT_7D_AGROMET] ON [INPUT_7D_AGROMET] ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION]);

/* ---------------------------------------------------------------------- */
/* INPUT_7D_FORECAST_PROVINCE											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_7D_FORECAST_PROVINCE')
BEGIN
	 DECLARE @reftable_21 nvarchar(60), @constraintname_21 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_7D_FORECAST_PROVINCE'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_21, @constraintname_21
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_21+' drop constraint '+@constraintname_21)
	   FETCH NEXT from refcursor into @reftable_21, @constraintname_21
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_7D_FORECAST_PROVINCE]
END


CREATE TABLE [INPUT_7D_FORECAST_PROVINCE]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[IPROVINCE_ID] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NOT NULL,
	[ITEMPERATURE_MAX] INT  NULL,
	[ITEMPERATURE_MIN] INT  NULL,
	[IWIND_DIRECTION] INT  NULL,
	[IWIND_SPEED] INT  NULL,
	[IRAIN_AREA] INT  NULL,
	[SDESC_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SDESC_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[IWEATHER_TYPE] INT  NULL,
	[ITEMPERATURE_TYPE] INT  NULL,
	[IWAVE_TYPE] INT  NULL,
	CONSTRAINT [INPUT_7D_FORECAST_PROVINCE_PK] PRIMARY KEY ([DTTIME_FORECAST],[IPROVINCE_ID])
);

CREATE INDEX [PK_INPUT_7D_FORECAST_PROVINCE] ON [INPUT_7D_FORECAST_PROVINCE] ([DTTIME_FORECAST],[IPROVINCE_ID]);

/* ---------------------------------------------------------------------- */
/* INPUT_4WKS_FORECAST											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_4WKS_FORECAST')
BEGIN
	 DECLARE @reftable_22 nvarchar(60), @constraintname_22 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_4WKS_FORECAST'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_22, @constraintname_22
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_22+' drop constraint '+@constraintname_22)
	   FETCH NEXT from refcursor into @reftable_22, @constraintname_22
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_4WKS_FORECAST]
END


CREATE TABLE [INPUT_4WKS_FORECAST]
(
	[DTTIME_FORECAST_FROM] DATETIME(16,3)  NOT NULL,
	[DTTIME_FORECAST_TO] DATETIME(16,3)  NOT NULL,
	[IGEOGRAPHIC_REGION] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NOT NULL,
	[ITEMP_MAX_LOWBOUND] INT  NULL,
	[ITEMP_MAX_HIGHBOUND] INT  NULL,
	[ITEMP_MIN_LOWBOUND] INT  NULL,
	[ITEMP_MIN_HIGHBOUND] INT  NULL,
	[IRAINFALL_LOWBOUND] INT  NULL,
	[IRAINFALL_HIGHBOUND] INT  NULL,
	[SDESC_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SDESC_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[IWEATHER_TYPE] INT  NULL,
	[ITEMPERATURE_TYPE] INT  NULL,
	[IWAVE_TYPE] INT  NULL,
	CONSTRAINT [INPUT_4WKS_FORECAST_PK] PRIMARY KEY ([DTTIME_FORECAST_FROM],[DTTIME_FORECAST_TO],[IGEOGRAPHIC_REGION])
);

CREATE INDEX [PK_INPUT_4WKS_FORECAST] ON [INPUT_4WKS_FORECAST] ([DTTIME_FORECAST_FROM],[DTTIME_FORECAST_TO],[IGEOGRAPHIC_REGION]);

/* ---------------------------------------------------------------------- */
/* INPUT_AGROMET											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_AGROMET')
BEGIN
	 DECLARE @reftable_23 nvarchar(60), @constraintname_23 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_AGROMET'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_23, @constraintname_23
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_23+' drop constraint '+@constraintname_23)
	   FETCH NEXT from refcursor into @reftable_23, @constraintname_23
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_AGROMET]
END


CREATE TABLE [INPUT_AGROMET]
(
	[IAGROMET_ID] INT  NOT NULL,
	[DTTIME_ISSUE] DATETIME(16,3)  NULL,
	[STITLE] VARCHAR(100)  NULL,
	[STITLE_ENG] VARCHAR(100)  NULL,
	[IUSER_ID] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NULL,
	[IAGROMET_TYPE] INT  NULL,
	[SDOC_LINK] VARCHAR(200)  NULL,
	CONSTRAINT [INPUT_AGROMET_PK] PRIMARY KEY ([IAGROMET_ID])
);

/* ---------------------------------------------------------------------- */
/* INPUT_7D_FORECAST_GEOREGION											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_7D_FORECAST_GEOREGION')
BEGIN
	 DECLARE @reftable_24 nvarchar(60), @constraintname_24 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_7D_FORECAST_GEOREGION'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_24, @constraintname_24
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_24+' drop constraint '+@constraintname_24)
	   FETCH NEXT from refcursor into @reftable_24, @constraintname_24
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_7D_FORECAST_GEOREGION]
END


CREATE TABLE [INPUT_7D_FORECAST_GEOREGION]
(
	[DTTIME_FORECAST] DATETIME(16,3)  NOT NULL,
	[IGEOGRAPHIC_REGION] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NULL,
	[SWEATHER_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWEATHER_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SWIND_WAVE_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWIND_WAVE_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWARNING_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[IWEATHER_TYPE] INT  NULL,
	[ITEMPERATURE_TYPE] INT  NULL,
	[IWAVE_TYPE] INT  NULL,
	CONSTRAINT [INPUT_7D_FORECAST_GEOREGION_PK] PRIMARY KEY ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION],[DTSAVE_TIME])
);

CREATE INDEX [PK_INPUT_7D_FORECAST_GEOREGION] ON [INPUT_7D_FORECAST_GEOREGION] ([DTTIME_FORECAST],[IGEOGRAPHIC_REGION],[DTSAVE_TIME]);

/* ---------------------------------------------------------------------- */
/* INPUT_7D_SUMMARY_WEATHER											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_7D_SUMMARY_WEATHER')
BEGIN
	 DECLARE @reftable_25 nvarchar(60), @constraintname_25 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_7D_SUMMARY_WEATHER'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_25, @constraintname_25
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_25+' drop constraint '+@constraintname_25)
	   FETCH NEXT from refcursor into @reftable_25, @constraintname_25
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_7D_SUMMARY_WEATHER]
END


CREATE TABLE [INPUT_7D_SUMMARY_WEATHER]
(
	[DTTIME_SUMMARY] DATETIME(16,3)  NOT NULL,
	[IGEOGRAPHIC_REGION] INT  NOT NULL,
	[DTSAVE_TIME] DATETIME(16,3)  NOT NULL,
	[IUSER_ID] INT  NULL,
	[SWEATHER_TH] VARCHAR(MAX)(2147483646)  NULL,
	[SWEATHER_ENG] VARCHAR(MAX)(2147483646)  NULL,
	[SDOC_LINK] VARCHAR(200)  NULL,
	[SRAIN_TH] VARCHAR(400)  NULL,
	[SRAIN_ENG] VARCHAR(400)  NULL,
	[STEMPERATURE_TH] VARCHAR(400)  NULL,
	[STEMPERATURE_ENG] VARCHAR(400)  NULL,
	CONSTRAINT [INPUT_7D_SUMMARY_WEATHER_PK] PRIMARY KEY ([DTTIME_SUMMARY],[IGEOGRAPHIC_REGION],[DTSAVE_TIME])
);

CREATE INDEX [PK_INPUT_7D_SUMMARY_WEATHER] ON [INPUT_7D_SUMMARY_WEATHER] ([DTTIME_SUMMARY],[IGEOGRAPHIC_REGION],[DTSAVE_TIME]);

/* ---------------------------------------------------------------------- */
/* INPUT_MONTHLY_SUMMARY_WEATHER											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'INPUT_MONTHLY_SUMMARY_WEATHER')
BEGIN
	 DECLARE @reftable_26 nvarchar(60), @constraintname_26 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'INPUT_MONTHLY_SUMMARY_WEATHER'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_26, @constraintname_26
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_26+' drop constraint '+@constraintname_26)
	   FETCH NEXT from refcursor into @reftable_26, @constraintname_26
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [INPUT_MONTHLY_SUMMARY_WEATHER]
END


CREATE TABLE [INPUT_MONTHLY_SUMMARY_WEATHER]
(
	[DTTIME_SUMMARY] DATETIME  NOT NULL,
	[DTSAVE_TIME] DATETIME  NULL,
	[IUSER_ID] INT  NOT NULL,
	[SOVERVIEW_TH] VARCHAR(MAX)  NULL,
	[SOVERVIEW_ENG] VARCHAR(MAX)  NULL,
	[SOVERVIEW_OF_MONTH_TH] VARCHAR(MAX)  NULL,
	[SOVERVIEW_OF_MONTH_ENG] VARCHAR(MAX)  NULL,
	[SDOC_LINK] VARCHAR(255)  NULL,
	[SDOC_LINK_ENG] VARCHAR(255)  NULL,
	CONSTRAINT [INPUT_MONTHLY_SUMMARY_WEATHER_PK] PRIMARY KEY ([DTTIME_SUMMARY])
);

/* ---------------------------------------------------------------------- */
/* ANNOUNCE_TYPE											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'ANNOUNCE_TYPE')
BEGIN
	 DECLARE @reftable_27 nvarchar(60), @constraintname_27 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'ANNOUNCE_TYPE'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_27, @constraintname_27
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_27+' drop constraint '+@constraintname_27)
	   FETCH NEXT from refcursor into @reftable_27, @constraintname_27
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [ANNOUNCE_TYPE]
END


CREATE TABLE [ANNOUNCE_TYPE]
(
	[IANNOUNCE_TYPE] INT  NOT NULL,
	[SDESC_TH] VARCHAR(MAX)  NOT NULL,
	[SDESC_ENG] VARCHAR(MAX)  NOT NULL,
	CONSTRAINT [ANNOUNCE_TYPE_PK] PRIMARY KEY ([IANNOUNCE_TYPE])
);

/* ---------------------------------------------------------------------- */
/* ANNOUNCE											*/
/* ---------------------------------------------------------------------- */


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'ANNOUNCE')
BEGIN
	 DECLARE @reftable_28 nvarchar(60), @constraintname_28 nvarchar(60)
	 DECLARE refcursor CURSOR FOR
	 select reftables.name tablename, cons.name constraintname
	  from sysobjects tables,
		   sysobjects reftables,
		   sysobjects cons,
		   sysreferences ref
	   where tables.id = ref.rkeyid
		 and cons.id = ref.constid
		 and reftables.id = ref.fkeyid
		 and tables.name = 'ANNOUNCE'
	 OPEN refcursor
	 FETCH NEXT from refcursor into @reftable_28, @constraintname_28
	 while @@FETCH_STATUS = 0
	 BEGIN
	   exec ('alter table '+@reftable_28+' drop constraint '+@constraintname_28)
	   FETCH NEXT from refcursor into @reftable_28, @constraintname_28
	 END
	 CLOSE refcursor
	 DEALLOCATE refcursor
	 DROP TABLE [ANNOUNCE]
END


CREATE TABLE [ANNOUNCE]
(
	[IANNOUNCE_ID] INT  NOT NULL,
	[SISSUE] VARCHAR(255)  NOT NULL,
	[STITLE] VARCHAR(MAX)  NOT NULL,
	[STITLE_ENG] VARCHAR(MAX)  NOT NULL,
	[DTTIME_START] DATETIME  NULL,
	[DTTIME_START_OVER] DATETIME  NULL,
	[DTTIME_END] DATETIME  NULL,
	[IUSER_ID] INT  NOT NULL,
	[SDESC_TH] VARCHAR(MAX)  NULL,
	[SDESC_ENG] VARCHAR(MAX)  NULL,
	[SDOC_LINK] VARCHAR(255)  NULL,
	[IANNOUNCE_TYPE] INT  NULL,
	[DTANNOUNCE_TIME] DATETIME  NULL,
	[SNOTES] VARCHAR(MAX)  NULL,
	[SNOTES_ENG] VARCHAR(MAX)  NULL,
	[SCONTACT_DETAIL] VARCHAR(MAX)  NULL,
	[SCONTACT_DETAIL_ENG] VARCHAR(MAX)  NULL,
	CONSTRAINT [ANNOUNCE_PK] PRIMARY KEY ([IANNOUNCE_ID])
);
